package com.ita.os.client.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ita.os.client.dao.MerchantDao;
import com.ita.os.client.po.Merchant;
import com.ita.os.client.po.Page;
import com.ita.os.client.util.DbUtil;

public class MerchantDaoImpl implements MerchantDao{

	@Override
	public List<Merchant> listMerchants(Page p,String type) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ( select ROWNUM rn, m.* from ( select * from merchant a left join maudit b on a.mid=b.mid where b.status=1");
		if(type != null)
			sb.append(" and a.type = '" + type + "'");
		sb.append(")m where ROWNUM<=? ) where rn >=?");
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<Merchant> listMer = null;
		try {
			con = DbUtil.connect();
			listMer = new ArrayList<Merchant>();
			String sql = sb.toString();
			//String sql = "select * from ( select ROWNUM rn, m.* from ( select * from merchant a left join maudit b on a.mid=b.mid where b.status=1)m where ROWNUM<=? ) where rn >=?";
			pst = con.prepareStatement(sql);
			pst.setInt(1, p.getCurPage()*p.getPageSize());
			pst.setInt(2, p.getStartPage());
			rs= pst.executeQuery();
			Merchant m = null;
			while (rs.next()) {
				m = new Merchant();
				m.setMid(rs.getInt("mid"));
				m.setMname(rs.getString("mname"));
				m.setType(rs.getString("type"));
				m.setAddress(rs.getString("address"));
				m.setTel(rs.getString("tel"));
				m.setLogo(rs.getString("logo"));
				m.setInfo(rs.getString("info"));
				listMer.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		DbUtil.free(con, pst, rs);
		}
		return listMer;
	}

	@Override
	public Merchant merchantInfo(int mid) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Merchant m = null;
		try {
			con = DbUtil.connect();
			String sql = "select * from merchant where mid=?";
			pst = con.prepareStatement(sql);
			pst.setInt(1, mid);
			rs= pst.executeQuery();
			while (rs.next()) {
				m = new Merchant();
				m.setMname(rs.getString("mname"));
				m.setType(rs.getString("type"));
				m.setAddress(rs.getString("address"));
				m.setTel(rs.getString("tel"));
				m.setLogo(rs.getString("logo"));
				m.setInfo(rs.getString("info"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, rs);
		}
		return m;
	}

	@Override
	public int countMerchant(String type) {
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) as count from merchant m left join maudit a on m.mid=a.mid where a.status=1");
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		int count = 0;
		if(type != null)
			sb.append(" and m.type = '" + type + "'");
		try {
			con = DbUtil.connect();
			//String sql = "select count(*) as count from merchant m left join maudit a on m.mid=a.mid where a.status=1";
			String sql = sb.toString();
			pst = con.prepareStatement(sql);
			rs= pst.executeQuery();
			rs.next();
			count = rs.getInt("count");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, rs);
		}
		return count;
	}
	

}
